﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;


namespace SQLServerDAL.Sys
{
    public class EnumItem
    {
        public EnumItem() { 

        }

        public string Add(Model.Sys.EnumItem _obj)
        {
            string result = "";

            string sqlstring = "INSERT INTO EnumItem (EnumItemId, EnumKey, EnumText, EnumEnText, EnumValue, SortIndex, SubEnum, ExtValue)";
            sqlstring += "VALUES(NEWID(), @EnumKey, @EnumText, @EnumEnText, @EnumValue, @SortIndex, @SubEnum, @ExtValue)";
            SqlParameter[] prams = { 
                SqlHelper.CreateInputParam("@EnumKey", SqlDbType.VarChar,_obj.EnumKey),
                SqlHelper.CreateInputParam("@EnumText", SqlDbType.VarChar,_obj.EnumText),
                SqlHelper.CreateInputParam("@EnumEnText", SqlDbType.VarChar,_obj.EnumEnText),
                SqlHelper.CreateInputParam("@EnumValue", SqlDbType.VarChar,_obj.EnumValue),
                SqlHelper.CreateInputParam("@SortIndex", SqlDbType.Decimal,_obj.SortIndex),
                SqlHelper.CreateInputParam("@SubEnum", SqlDbType.VarChar,_obj.SubEnum),
                SqlHelper.CreateInputParam("@ExtValue", SqlDbType.VarChar,_obj.ExtValue), 
            };

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlstring, prams);
                result = "succeeded";
            }
            catch (Exception ex)
            {
                result = ex.ToString();
            }

            return result;
        }

        public string Update(Model.Sys.EnumItem _obj)
        {
            string result = "";

            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("UPDATE EnumItem SET ");
                    strSql.Append("EnumKey = @EnumKey,");
                    strSql.Append("EnumText = @EnumText,");
                    strSql.Append("EnumEnText = @EnumEnText,");
                    strSql.Append("EnumValue = @EnumValue,");
                    strSql.Append("SortIndex = @SortIndex,");
                    strSql.Append("SubEnum = @SubEnum,");
                    strSql.Append("ExtValue = @ExtValue");
                    strSql.Append(" WHERE EnumItemId = @EnumItemId;");

                    SqlParameter[] prams = {
                        SqlHelper.CreateInputParam("@EnumItemId", SqlDbType.UniqueIdentifier,new Guid(_obj.EnumItemId)),
                        SqlHelper.CreateInputParam("@EnumKey", SqlDbType.VarChar,_obj.EnumKey),
                        SqlHelper.CreateInputParam("@EnumText", SqlDbType.VarChar,_obj.EnumText),
                        SqlHelper.CreateInputParam("@EnumEnText", SqlDbType.VarChar,_obj.EnumEnText),
                        SqlHelper.CreateInputParam("@EnumValue", SqlDbType.VarChar,_obj.EnumValue),
                        SqlHelper.CreateInputParam("@SortIndex", SqlDbType.Decimal,_obj.SortIndex),
                        SqlHelper.CreateInputParam("@SubEnum", SqlDbType.VarChar,_obj.SubEnum),
                        SqlHelper.CreateInputParam("@ExtValue", SqlDbType.VarChar,_obj.ExtValue), 
                   }; 

                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql.ToString(), prams);

                    trans.Commit();
                    result = "succeeded";
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();

            return result;
        }

        public Model.Sys.EnumItem GetObjById(string enumItemId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM EnumItem WHERE EnumItemId = @EnumItemId");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@EnumItemId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(enumItemId);

            Model.Sys.EnumItem _obj = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _obj = new Model.Sys.EnumItem();
                    _obj.EnumItemId = rd["EnumItemId"].ToString();
                    _obj.EnumKey = rd["EnumKey"].ToString();
                    _obj.EnumText = rd["EnumText"].ToString();
                    _obj.EnumEnText = rd["EnumEnText"].ToString();
                    _obj.EnumValue = rd["EnumValue"].ToString();
                    _obj.SortIndex = decimal.Parse(rd["SortIndex"].ToString());
                    _obj.SubEnum = rd["SubEnum"].ToString();
                    _obj.ExtValue = rd["ExtValue"].ToString();
                }
            }
            catch
            {
                return null;
            }
            return _obj;
        }

        public void Delete(string enumDefId)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@EnumItemId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(enumDefId);

            strSql.Append("DELETE FROM EnumItem WHERE [EnumItemId] = @EnumItemId;");

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
        }
    }
}
